SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF

DECLARE
	l_total_extracted	NUMBER;
	l_total_imported	NUMBER;		
	l_total_extracted_2	NUMBER;
	l_total_imported_2	NUMBER;		
	l_func_id			VARCHAR2(100):='CAPPOREC_LOCATION';
	l_code VARCHAR2(100):=UPPER('&1');
BEGIN
	SELECT COUNT(1)
	INTO l_total_extracted        
	from   HR_LOCATIONS_ALL hl, HR_ORGANIZATION_UNITS hou
   	where  hl.business_group_id = hou.business_group_id
    /*	AND    upper(hou.name) = (select 'BG_'||UPPER(TO_CHAR(set_of_books_id))
    					   		  from   gl_sets_of_books
    					   		  where  upper(short_name) = upper(l_code)) ;  */
	--AND    upper(hou.name) = upper(l_code);       					   		  
	AND    upper(hou.name) = 'BG_'||upper(substr(l_code, -4));
	
	SELECT COUNT(1) 
	INTO l_total_imported
	FROM CRST_HR_LOCATIONS_ALL;

	SELECT COUNT(1)
	INTO l_total_extracted_2       
	from   HR_LOCATIONS_ALL_TL 
    	where  LOCATION_ID in (SELECT hl.location_id
				    from   HR_LOCATIONS_ALL hl, HR_ORGANIZATION_UNITS hou
   				    where  hl.business_group_id = hou.business_group_id
    				    /*AND    upper(hou.name) = (	select 'BG_'||UPPER(TO_CHAR(set_of_books_id))
    					   							from   gl_sets_of_books
    					   							where  upper(short_name) = upper(l_code))   */
				--AND    upper(hou.name) = upper(l_code)    					   							
				AND    upper(hou.name) = 'BG_'||upper(substr(l_code, -4))
				   );
	
	SELECT COUNT(1) 
	INTO l_total_imported_2
	FROM CRST_HR_LOCATIONS_ALL_TL;

	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported));
	dbms_output.put_line('Total Extracted_2: '||TO_CHAR(l_total_extracted_2)) ;
	dbms_output.put_line('Total Imported_2:  '||TO_CHAR(l_total_imported_2));
	IF l_total_extracted <> l_total_imported THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;	
	IF l_total_extracted_2 <> l_total_imported_2 THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;	
						
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);			
END;
/
